﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Practices.EnterpriseLibrary.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Model;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;


namespace DAL
{
   public class ArticlescrapDAL
    {
        /// <summary>z
        /// </summary>
        Database db = DatabaseFactory.CreateDatabase("constring");

        /// <summary>
        /// 获取面试经验文章
        /// </summary>
        /// <returns></returns>
        public List<Articlescrap> GetArticlescrap(int pageindex, int pagesize, int UserId)
        {
            try
            {
                if (pageindex < 1)
                {
                    pageindex = 1;
                }
                int start = (pageindex - 1) * pagesize + 1;
                int end = pageindex * pagesize;
                string sql = "select * from( select ROW_NUMBER() over(order by Articlescrap_Id)i,a.Articlescrap_Id,a.Articlescrap_Title,a.Articlescrap_Content,a.Articlescrap_date,a.Login_Id,a.Rated_Id,u.Login_Nickname from Articlescrap a join Users u on a.Login_Id=u.Login_Id where 1=1";
                if(UserId!=0)
                {
                    sql += " and a.Login_Id=" + UserId + "";
                } 
                sql += " )b where 1=1";
                sql += " and i between " + start + " and " + end + "";
                DataSet ds = db.ExecuteDataSet(CommandType.Text, sql);
                List<Articlescrap> list = new List<Articlescrap>();
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    Articlescrap art = new Articlescrap();
                    art.Articlescrap_Id = (int)dr["Articlescrap_Id"];
                    art.Articlescrap_Title = dr["Articlescrap_Title"].ToString();
                    art.Articlescrap_Content = dr["Articlescrap_Content"].ToString();
                    art.Articlescrap_date =Convert.ToDateTime(dr["Articlescrap_date"]);
                    art.Login_Id = (int)dr["Login_Id"];
                    art.Login_Nickname = dr["Login_Nickname"].ToString();
                    art.Rated_Id =(int)dr["Rated_Id"];
                    list.Add(art);
                }
                return list;
            }
            catch (Exception)
            { 
                throw;
            }

        }

        /// <summary>
        /// 带条件获取总记录数
        /// </summary>
        /// <returns></returns>
        public int count(int UserId)
        {  
            try
            {
                string sql = "select count(*) from Articlescrap where 1=1";
                if (UserId != 0)
                {
                    sql += " and Login_Id=" + UserId + "";
                } 
                int result = Convert.ToInt32(db.ExecuteScalar(CommandType.Text, sql));
                return result;
            }
            catch (Exception)
            {    
                throw;
            }
        }

        /// <summary>
        /// 根据Id删除面试经验文章
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DelArticlescrap(int id)
        {
            try
            {
                int result = Convert.ToInt32(db.ExecuteNonQuery(CommandType.Text, "delete from Articlescrap where Articlescrap_Id=" + id + ""));
                return result;
            }
            catch (Exception)
            {   
                throw;
            }
        }
       /// <summary>
       /// 根据Id获取面试经验文章信息
       /// </summary>
       /// <param name="id"></param>
       /// <returns></returns>
        public List<Articlescrap> GetArtById(int id)
        {
            try
            {
                DataSet ds = db.ExecuteDataSet(CommandType.Text, "select a.Articlescrap_Id,a.Articlescrap_Title,a.Articlescrap_Content,a.Articlescrap_date,a.Login_Id,a.Rated_Id,u.Login_Nickname from Articlescrap a join Users u on a.Login_Id=u.Login_Id where a.Articlescrap_Id="+id+"");
                List<Articlescrap> list = new List<Articlescrap>();
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    Articlescrap art = new Articlescrap();
                    art.Articlescrap_Id = (int)dr["Articlescrap_Id"];
                    art.Articlescrap_Title = dr["Articlescrap_Title"].ToString();
                    art.Articlescrap_Content = dr["Articlescrap_Content"].ToString();
                    art.Articlescrap_date =Convert.ToDateTime(dr["Articlescrap_date"].ToString());
                    art.Login_Id = (int)dr["Login_Id"];
                    art.Login_Nickname = dr["Login_Nickname"].ToString();
                    art.Rated_Id = (int)dr["Rated_Id"];
                    list.Add(art);
                }
                return list;

            }
            catch (Exception)
            {
                
                throw;
            }
        }
        /// <summary>
        /// 添加面试经验文章
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int addinfo(Articlescrap artic)
        {
            try
            {
                int result = Convert.ToInt32(db.ExecuteNonQuery(CommandType.Text, "INSERT INTO Articlescrap(Articlescrap_Title,Articlescrap_Content,Articlescrap_date,Login_Id)VALUES('" + artic.Articlescrap_Title + "','" + artic.Articlescrap_Content + "','" + artic.Articlescrap_date + "','" + artic.Login_Id + "')"));
                return result;
            }
            catch (Exception)
            {
                throw;
            }
        }

    }
}
